<?
class Facture extends Model {

    public function __construct($id=""){
      $conn = new Database();$mysql = $conn->getConnection();
      if($id!=""){
          $query = "SELECT * FROM facture WHERE _id = '$id';";
          $res = $mysql->query($query);
          if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
          $user = $res->fetch_object();
          foreach($user as $var=>$val) $this->$var = $val;
      }
    }
    public function save(){
      $conn = new Database();$mysql = $conn->getConnection();
      foreach($this as $var=>$val){
        $vars .= "$var,";
        $vals .= "\"".addslashes($val)."\",";
        $update .= "$var=\"".addslashes($val)."\",";
      }
      $vars = substr($vars,0,strlen($vars)-1);
      $vals = substr($vals,0,strlen($vals)-1);
      $update = substr($update,0,strlen($update)-1);
      $query = "INSERT INTO facture ($vars) VALUES ($vals) ON DUPLICATE KEY UPDATE $update;";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if(!isset($this->_id))$this->_id = $mysql->insert_id;
    }
    public function getLines(){
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT * FROM line_facture WHERE facture_id = '$this->_id' ORDER BY _id;";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return array();
      while($line = $res->fetch_object()){
        $aLines[] = $line;
      }
      return $aLines;
    }
    public static function getAll($date=""){
      if($date!=""){
        $dateCondition = "AND date LIKE \"$date%\" ";
      }else{
        $dateCondition = "";
      }
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT _id FROM facture WHERE user_id = \"".$_SESSION["user_id"]."\" $dateCondition ORDER BY date DESC;";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return array();
      while($line = $res->fetch_object()){
        $devis = new Facture($line->_id);
        $aDevis[] = $devis;
      }
      return $aDevis;
    }
    public static function getAllPayed($date1="",$date2=""){
      if($date1 != "" && $date2 != ""){
        $dateCondition = "AND (paiement_client.date BETWEEN \"$date1\" AND \"$date2\") ";
      }elseif($date1!=""){
        $dateCondition = "AND paiement_client.date LIKE \"$date1%\" ";
      }else{
        $dateCondition = "";
      }
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT facture._id FROM facture INNER JOIN paiement_client ON paiement_client._facture = facture._id "
          ."WHERE user_id = \"".$_SESSION["user_id"]."\" $dateCondition ORDER BY paiement_client.date DESC;";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return array();
      while($line = $res->fetch_object()){
        $devis = new Facture($line->_id);
        $aDevis[] = $devis;
      }
      return $aDevis;
    }
    public static function getByContact($contact,$date=""){
      if($date!=""){
        $dateCondition = "AND paiement_client.date LIKE \"$date%\" ";
      }else{
        $dateCondition = "";
      }
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT facture._id FROM facture INNER JOIN paiement_client ON paiement_client._facture = facture._id "
          ."WHERE user_id = \"".$_SESSION["user_id"]."\" $dateCondition "
          ."AND contact_id = '$contact' ORDER BY paiement_client.date DESC;";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return array();
      while($line = $res->fetch_object()){
        $devis = new Facture($line->_id);
        $aDevis[] = $devis;
      }
      return $aDevis;
    }
    public function remove(){
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "DELETE FROM facture WHERE _id = '$this->_id';";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      $query = "DELETE FROM line_facture WHERE facture_id = '$this->_id';";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
    }
    /**
     * Enregistre le paiement du client
   */
    public function paiement(){
      $date = date("Y-m-d");
      $query = "INSERT INTO paiement_client (_facture,date) VALUES (\"$this->_id\",\"$date\");";
      $conn = new Database();$mysql = $conn->getConnection();
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
    }
    public function removePaiement(){
      $query = "DELETE FROM paiement_client WHERE _facture = \"$this->_id\";";
      $conn = new Database();$mysql = $conn->getConnection();
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
    }
    public function isPayed(){
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT _id FROM paiement_client WHERE _facture = \"$this->_id\";";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return false;
      return true;
    }
    public function isLate(){
      if(strtotime($this->echeance) < strtotime(date("Y-m-d"))) return true;
      return false;
    }
    public static function getPending(){
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT _id FROM facture WHERE _id NOT IN (select _facture FROM paiement_client);";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return array();
      while($line = $res->fetch_object()){
        $aFactures[] = new Facture($line->_id);
      }
      return $aFactures;
    }
    public function getPaiement(){
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT * FROM paiement_client WHERE _facture = '$this->_id';";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      if($res->num_rows == 0) return array();
      $line = $res->fetch_object();
      return $line;
    }
    public function addLine($desc,$qte,$ht,$tva,$classe){
     
      $conn = new Database();$mysql = $conn->getConnection();
      $desc = str_replace("<p>","",$desc);
      $desc = str_replace("</p>","<br/>",$desc);
      $desc = html_entity_decode($desc);
      $desc = utf8_encode($desc);
      $vars = "facture_id,description,qte,montant_ht,taux_tva,classe";
      $vals = "'$this->_id',\"".addslashes($desc)."\",\"$qte\",\"$ht\",\"$tva\",\"$classe\"";
      $query = "INSERT INTO line_facture ($vars) VALUES ($vals);";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
    }
    public static function getCA(){
      $conn = new Database();$mysql = $conn->getConnection();
      $query = "SELECT sum(montant_ttc) as ttc FROM facture INNER JOIN paiement_client ON facture._id = paiement_client._facture "
          ."WHERE user_id = \"".$_SESSION["user_id"]."\";";
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
      $somme = $res->fetch_object()->ttc;
      return $somme;
    }
    public function top5($year){
      $query = "SELECT contact_id, sum(montant_ht) as somme "
          ."FROM facture INNER JOIN paiement_client ON facture._id = paiement_client._facture "
          ."WHERE paiement_client.date LIKE \"$year%\" "
          ."AND user_id = '$_SESSION[user_id]' "
          ."GROUP BY contact_id "
          ."LIMIT 5;";
          
      $conn = new Database();$mysql = $conn->getConnection();
      $res = $mysql->query($query);if($mysql->errno != 0) throw new Exception ("Erreur MySQL : <br>$query<br>".$mysql->error);
      while($line = $res->fetch_object()){
        $contact = new Contact($line->contact_id);
        $data[$contact->__toString()] = $line->somme;
      }
      return $data;
      
    }
}
?>